﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using RecToTPI.Class;
using RecToTPI.Utility;
using System.Text.RegularExpressions;
using System.Text;
using NLog;

namespace RecToTPI
{
    public partial class Form1 : Form
    {
        private string connectionStringFormat = "Server={0},{1};Database={2};User Id={3};Password={4};";       // Access连接串
        private string connectionString = string.Empty;
        private string SQLServerTableName = string.Empty;               // 当前选定的SQL Server数据表

        public Form1()
        {
            this.InitializeComponent();
            backgroundWorker1.WorkerReportsProgress = true;
            backgroundWorker1.WorkerSupportsCancellation = true;
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private bool CheckSqlServerConnect(string serverIP, int port, string databaseName, string userName, string password)
        {
            bool result = false;
            connectionString = string.Format(connectionStringFormat, serverIP, port, databaseName, userName, password);
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                result = true;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
            return result;
        }

        /// <summary>
        /// 计算文件MD5值
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <returns></returns>
        #region 计算文件MD5值
        public static string FileToMD5(string path)
        {
            try
            {
                using (System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Open))
                {
                    System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
                    byte[] b = md5.ComputeHash(fs);
                    return System.BitConverter.ToString(b).Replace("-", "");
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }
        #endregion

        /// <summary>
        /// 数据库列表选中发生改变的处理
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        #region 数据库列表选中发生改变的处理
        private void ComboBox_DBList_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
        #endregion

        /// <summary>
        /// 选择源文件所在的目录
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        #region 选择源文件所在的目录
        private void btnSelectRecFile_Click(object sender, EventArgs e)
        {
            ofdSelectRecFile.Filter = "Tfo files (*.tfo)|*.tfo|所有文件(*.*)|*.*";
            DialogResult result = this.ofdSelectRecFile.ShowDialog();
            if (result == DialogResult.OK)
            {
                string strFileName = this.ofdSelectRecFile.FileName;

                if (strFileName != "")
                {
                    this.tbRecFile.Text = strFileName;
                }
            }
        }
        #endregion

        /// <summary>
        /// 开始对应的处理
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnStart_Click(object sender, EventArgs e)
        {
            try
            {

                SQLServerTableName = combSqlServerTable.SelectedValue.ToString(); // 获取选定的TPI表名称

                if (backgroundWorker1.IsBusy != true)
                {
                    // 获取 REC 记录总数
                    StreamReader srRecFile = new StreamReader(tbRecFile.Text);
                    int iLineCount = 0;
                    while ((srRecFile.ReadLine()) != null)
                    {
                        iLineCount++;
                    }
                    srRecFile.Dispose();    // 释放文件资源
                    // 获取后台任务参数
                    WorkerArgument argument = new WorkerArgument { RecordCount = iLineCount, RecFileFullName = tbRecFile.Text, TableName = SQLServerTableName };

                    // Start the asynchronous operation.
                    backgroundWorker1.RunWorkerAsync(argument);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导入TXT发生异常 异常信息：" + ex.Message + " 堆栈信息：" + ex.StackTrace);
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            if (backgroundWorker1.WorkerSupportsCancellation)
            {
                // Cancel the asynchronous operation.
                backgroundWorker1.CancelAsync();
            }
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            BackgroundWorker worker = sender as BackgroundWorker;
            WorkerArgument argument = e.Argument as WorkerArgument;
            string sqlMonitor = string.Empty;
            try
            {
                if (argument == null)
                {
                    MessageBox.Show("导入数据所需参数设置不正确！");
                    return;
                }
                // 获取当前SQL表字段信息
                string strSql = $"SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='{argument.TableName}' ";
                IDataReader columnReader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, strSql);
                Dictionary<string, string> columnDictionary = new Dictionary<string, string>();
                while (columnReader.Read())
                {
                    columnDictionary.Add(columnReader["COLUMN_NAME"].ToString(), columnReader["COLUMN_NAME"].ToString());
                }
                columnReader.Close();
                // 开启 REC 文件读取器
                StreamReader srRecFile = new StreamReader(argument.RecFileFullName, Encoding.Default);
                string strLine = string.Empty;
                List<string> fieldNameList = new List<string>();    // 字段列表
                List<string> fieldValueList = new List<string>();   // 字段值列表
                int nRows = 0;   // 导入进度
                Regex recordReg = new Regex(@"^R\^$");
                Regex fieldReg = new Regex(@"(?<FieldTypeSegment>(?<FieldType>\d*)F\^)|((?<FieldLengthSegment>(?<FieldLength>\d*)L)+(?<FieldContent>.*?)\^)");
                int iProcessedCount = 0;
                int iLastRecordNumber = Convert.ToInt32(txtLastRecordNumber.Text.Trim());
                while ((strLine = srRecFile.ReadLine()) != null)
                {
                    nRows++;
                    // 检查是否有挂起的取消操作
                    if (worker.CancellationPending)
                    {
                        e.Cancel = true;
                        break;
                    }
                    if (strLine.Contains("4LPQDD"))
                    {
                        continue;
                    }
                    // 包含记录标识但非首记录时保存记录信息
                    if (recordReg.IsMatch(strLine))
                    {
                        if (fieldNameList.Count > 0)
                        {
                            // 插入数据前，如果前一字段未填充，则将前一字段赋为空值
                            if (fieldNameList.Count > fieldValueList.Count)
                            {
                                fieldValueList.Add(string.Empty);
                            }

                            // 遍历去除数据表中不存在的列
                            for (int i = fieldNameList.Count - 1; i >= 0; i--)
                            {
                                if (!columnDictionary.Keys.Contains(fieldNameList[i]))
                                {
                                    fieldNameList.Remove(fieldNameList[i]);
                                    fieldValueList.Remove(fieldValueList[i]);
                                }
                            }

                            SqlParameter[] parameter = new SqlParameter[fieldNameList.Count];    // 使用参数化查询进行插入操作
                            for (int i = 0; i < fieldNameList.Count; i++)
                            {
                                parameter[i] = new SqlParameter
                                {
                                    ParameterName = fieldNameList[i].Replace("－", "").Replace("（", "").Replace("）", "").Replace("、", ""),
                                    DbType = DbType.String,
                                    Value = fieldValueList[i]
                                };
                            }
                            string strInsertSql = string.Format("insert into [{0}]({1}) values({2})",
                                                            argument.TableName,
                                                            fieldNameList.Aggregate(string.Empty, (a, b) => $"{a},{b}").Trim(','),
                                                            fieldNameList.Aggregate(string.Empty, (a, b) => $"{a},@{b}").Trim(','));
                            sqlMonitor = strInsertSql;
                            iProcessedCount++;
                            if (iProcessedCount > iLastRecordNumber)
                            {
                                if (SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, strInsertSql, parameter) > 0)
                                {
                                    int percentComplete = (int)((float)nRows / argument.RecordCount * 100);
                                    worker.ReportProgress(percentComplete);
                                }
                            }
                            else
                            {
                                int percentComplete = (int)((float)nRows / argument.RecordCount * 100);
                                worker.ReportProgress(percentComplete);
                            }
                            fieldNameList.Clear();
                            fieldValueList.Clear();
                        }
                    }
                    else
                    {
                        MatchCollection mc = fieldReg.Matches(strLine);
                        foreach (Match mat in mc)
                        {
                            if (mat.Groups["FieldType"].Success)
                            {
                                int iFieldType;
                                string sFieldType = mat.Groups["FieldType"].Value;
                                // 字段类型格式错误时，跳过此记录
                                if (!int.TryParse(sFieldType, out iFieldType))
                                {
                                    continue;
                                }
                                // 获取下一字段名称前，如果前一字段未填充，则将前一字段赋为空值
                                if (fieldNameList.Count > fieldValueList.Count)
                                {
                                    fieldValueList.Add(string.Empty);
                                }
                                if (!fieldNameList.Contains(Tools.TFOFormatDict[iFieldType]))
                                {
                                    // 获取字段名称
                                    fieldNameList.Add(Tools.TFOFormatDict[iFieldType]);
                                }
                            }

                            if (mat.Groups["FieldContent"].Success)
                            {
                                // 获取到新字段时，值列表增加，否则在最后一个值列表记录中追加文本
                                if (fieldNameList.Count > fieldValueList.Count)
                                {
                                    fieldValueList.Add(mat.Groups["FieldContent"].Value);
                                }
                                else
                                {
                                    fieldValueList[fieldValueList.Count - 1] += mat.Groups["FieldContent"].Value;
                                }
                            }
                        }
                    }
                }
                // 插入最后一条记录
                if (fieldNameList.Count > 0)
                {
                    // 插入数据前，如果前一字段未填充，则将前一字段赋为空值
                    if (fieldNameList.Count > fieldValueList.Count)
                    {
                        fieldValueList.Add(string.Empty);
                    }

                    // 遍历去除数据表中不存在的列
                    for (int i = fieldNameList.Count - 1; i >= 0; i--)
                    {
                        if (!columnDictionary.Keys.Contains(fieldNameList[i]))
                        {
                            fieldNameList.Remove(fieldNameList[i]);
                            fieldValueList.Remove(fieldValueList[i]);
                        }
                    }

                    SqlParameter[] parameter = new SqlParameter[fieldNameList.Count];    // 使用参数化查询进行插入操作
                    for (int i = 0; i < fieldNameList.Count; i++)
                    {
                        parameter[i] = new SqlParameter
                        {
                            ParameterName = fieldNameList[i].Replace("－", "").Replace("（", "").Replace("）", "").Replace("、", ""),
                            DbType = DbType.String,
                            Value = fieldValueList[i]
                        };
                    }
                    string strInsertSql = string.Format("insert into [{0}]({1}) values({2})",
                                                            argument.TableName,
                                                            fieldNameList.Aggregate(string.Empty, (a, b) => $"{a},{b}").Trim(','),
                                                            fieldNameList.Aggregate(string.Empty, (a, b) => $"{a},@{b}").Trim(','));
                    sqlMonitor = strInsertSql;
                    iProcessedCount++;
                    if (iProcessedCount > iLastRecordNumber)
                    {
                        if (SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, strInsertSql, parameter) > 0)
                        {
                            int percentComplete = (int)((float)nRows / argument.RecordCount * 100);
                            worker.ReportProgress(percentComplete);
                        }
                    }
                    else
                    {
                        int percentComplete = (int)((float)nRows / argument.RecordCount * 100);
                        worker.ReportProgress(percentComplete);
                    }
                    fieldNameList.Clear();
                    fieldValueList.Clear();
                }
            }
            catch (Exception ex)
            {
                LogManager.GetCurrentClassLogger().Error($"Message:{ex.Message} StackTrace:{ex.StackTrace} InsertSQL:{sqlMonitor}");
                MessageBox.Show("导入Excel发生异常 异常信息：" + ex.Message + " 堆栈信息：" + ex.StackTrace);
            }
        }

        private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            this.ProgressBar1.Value = e.ProgressPercentage;
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            MessageBox.Show("数据导入完成！");
        }

        private void btnConnectSqlServer_Click(object sender, EventArgs e)
        {
            try
            {
                string serverIP = txtSqlServerIP.Text.Trim();
                if (string.IsNullOrEmpty(txtSqlServerPort.Text))
                {
                    MessageBox.Show("请填写SQL Server 端口号，默认为1433。");
                    return;
                }
                int iPort;
                if (!int.TryParse(txtSqlServerPort.Text, out iPort))
                {
                    MessageBox.Show("端口号格式不正确！");
                    return;
                }
                if (!CheckSqlServerConnect(serverIP, iPort, txtSqlServerDatabase.Text, txtSqlServerUserName.Text,
                    txtSqlServerPassword.Text))
                {
                    MessageBox.Show("SQL Server 数据库连接失败！");

                }
                else
                {
                    MessageBox.Show("SQL Server 数据库连接成功！");
                    this.BindSqlServerTableList();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void BindSqlServerTableList()
        {
            string databaseName = txtSqlServerDatabase.Text.Trim();
            string strSql = string.Format("SELECT Name FROM {0}..SysObjects Where XType='U' ORDER BY Name", databaseName);
            DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, strSql);
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                this.combSqlServerTable.ValueMember = "Name";
                this.combSqlServerTable.DisplayMember = "Name";
                this.combSqlServerTable.DataSource = dt;
            }
        }
    }
}